<?php
/**
 * Description of the Query-class
 *
 * @author Machiel
 */

class QueryException extends Exception {

}

class Query {

    /**
     *
     * @var PDO
     */
    protected $db;

    /**
     *
     * @param string $host ip of database
     * @param string $database name of db
     * @param string $username username to login
     * @param string $password password to login
     */
    function __construct($host, $database, $username, $password) {
        try {
            $this->db = new PDO("mysql:host=$host;dbname=$database",
                    $username, $password);
        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    /**
     *
     * @param int $id
     * @return User
     */
    public function getUserById($id) {
        try {
            $qUser = $this->db->prepare("
                SELECT *
                FROM user
                WHERE id = :id
            ");
            $qUser->bindParam(":id", $id);
            $qUser->execute();

            if($qUser->rowCount() != 1) {
                throw new QueryException("No user found with this id-number.");
            }

            $rUser = $qUser->fetch(PDO::FETCH_ASSOC);

            return new User($rUser['id'], $rUser['email'],
                    $rUser['password'], new Date($rUser['register_date']));


        } catch(PDOException $e) {
            print_r($e->getMessage());
        }

        return $user;
    }

    /**
     * Selects all Characters from the user with the userid $uid
     * @param int $uid
     * @return Character[]
     * @todo Make $uid from User
     */
    public function getCharactersFromUserId($uid) {
        try {
            $qCharacters = $this->db->prepare("
                SELECT *
                FROM `character`
                WHERE userid = :uid
            ");
            $qCharacters->bindParam(":uid", $uid);
            $qCharacters->execute();

            $characters = array();

            while($rCharacter = $qCharacters->fetch(PDO::FETCH_ASSOC)) {
                $character = new Character($rCharacter['id'], $rCharacter['name'],
                        $rCharacter['locationid']);
                $characters[$rCharacter['id']] = $character;
            }

            return $characters;
        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    /**
     * Selects the Location in which Character is located
     * @param Character $char
     * @return Location
     */
    public function getLocationFromCharacter(Character $char) {
        try {
            $qLocation = $this->db->prepare("
                SELECT *
                FROM `location`
                WHERE id = :locid
            ");
            $qLocation->bindParam(":locid", $char->getLocationId());
            $qLocation->execute();

            if($qLocation->rowCount() != 1) {
                throw new QueryException("Location not found.");
            }
            $rLocation = $qLocation->fetch(PDO::FETCH_ASSOC);
            return new Location($rLocation['id'], $rLocation['x'],
                    $rLocation['y']);

        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    /**
     * Selects all Characters on a certain Location
     * @param Location $loc
     * @return Character[]
     */
    public function getCharactersFromLocation(Location $loc) {
        try {
            $qCharacters = $this->db->prepare("
                SELECT *
                FROM `character`
                WHERE locationid = :id
            ");
            $qCharacters->bindParam(":id", $loc->getId());
            $qCharacters->execute();

            $characters = array();

            while($rCharacter = $qCharacters->fetch(PDO::FETCH_ASSOC)) {
                $character = new Character($rCharacter['id'], $rCharacter['name'],
                        $rCharacter['locationid']);
                $characters[] = $character;
            }

            return $characters;

        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    /**
     * Selects all Characters on a certain Location, and selects their names as given by the user.
     * @param Location $loc
     * @param Character $char
     * @return Characters[]
     * @todo Name fixing in Character class
     */
    public function getCharactersFromLocationWithNames(Location $loc,
            Character $char) {
        try {
            $qCharacters = $this->db->prepare("
                SELECT c.id, c.locationid, cn.name as name
                FROM `character` as c LEFT JOIN `charactername` as cn
                    ON c.id = cn.nameid AND (cn.characterid =:cid OR cn.characterid IS NULL)
                WHERE c.locationid = :id
            ");
            $qCharacters->bindParam(":id", $loc->getId());
            $qCharacters->bindParam(":cid", $char->getId());
            $qCharacters->execute();

            $characters = array();

            while($rCharacter = $qCharacters->fetch(PDO::FETCH_ASSOC)) {
                if($rCharacter['id'] == $char->getId()) {
                    $character = $char;
                } else {
                    /**
                     * @todo Move this to Character class
                     */
                    if(empty($rCharacter['name']))
                        $name = "20 year old man";
                    else
                        $name = $rCharacter['name'];

                    $character = new Character($rCharacter['id'], $name,
                            $rCharacter['locationid']);
                }
                $characters[] = $character;
            }

            return $characters;

        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    /**
     * Selects a character with a certain ID, and the name as given by the user.
     * @param int $charid
     * @param Character $char
     * @return Character
     */
    public function getCharacterFromIdWithName($charid, Character $char) {
        try {
            $qCharacters = $this->db->prepare("
                SELECT c.id, c.locationid, cn.name as name
                FROM `character` as c LEFT JOIN `charactername` as cn
                    ON c.id = cn.nameid AND (cn.characterid =:cid OR cn.characterid IS NULL)
                WHERE c.id = :id
            ");
            $qCharacters->bindParam(":id", $charid);
            $qCharacters->bindParam(":cid", $char->getId());
            $qCharacters->execute();

            if($qCharacters->rowCount() != 1) {
                throw new QueryException("No character found with this ID");
            }

            $rCharacter = $qCharacters->fetch(PDO::FETCH_ASSOC);

            /**
             * @todo Move this to character class
             */
            if($rCharacter['id'] == $char->getId()) {
                $character = $char;
            } else {

                if(empty($rCharacter['name']))
                    $name = "20 year old man";
                else
                    $name = $rCharacter['name'];

                $character = new Character($rCharacter['id'], $name,
                        $rCharacter['locationid']);
            }

            return $character;

        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    /**
     * Updates a character, edits by the user. (Description, displayed name.)
     * @param Character $update
     * @param Character $char
     */
    public function setCharacterFromIdWithName(Character $update, Character $char) {
        try {
            $qCharacterName = $this->db->prepare("
                SELECT nameid
                FROM charactername
                WHERE characterid = :cid
                AND nameid = :nid
            ");
            $qCharacterName->bindParam(":cid", $char->getId());
            $qCharacterName->bindParam(":nid", $update->getId());
            $qCharacterName->execute();

            if($qCharacterName->rowCount() != 0) {
                $this->updateCharacterFromIdWithName($update, $char);
            } else {
                $this->insertCharacterFromIdWithName($update, $char);
            }
        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    /**
     * Inserts a character name into the charactername table, when it does not<br/>
     * exist yet
     * @param Character $update
     * @param Character $char
     */
    public function insertCharacterFromIdWithName(Character $update, Character $char) {
        try {
            $qCharacterName = $this->db->prepare("
                INSERT INTO charactername
                (characterid, nameid, name)
                VALUES
                (:cid, :nid, :name)
            ");
            $qCharacterName->bindParam(":cid", $char->getId());
            $qCharacterName->bindParam(":nid", $update->getId());
            $qCharacterName->bindParam(":name", $update->getName());
            $qCharacterName->execute();
        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    /**
     * Updates a character name in the charactername table
     * @param Character $update
     * @param Character $char
     */
    public function updateCharacterFromIdWithName(Character $update, Character $char) {
        try {
            $qCharacterName = $this->db->prepare("
                UPDATE `charactername` SET
                name = :name
                WHERE
                characterid = :cid AND nameid = :nid
            ");
            $qCharacterName->bindParam(":cid", $char->getId());
            $qCharacterName->bindParam(":nid", $update->getId());
            $qCharacterName->bindParam(":name", $update->getName());
            $qCharacterName->execute();
        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    public function registerUser($info) {
        try {
            $qRegister = $this->db->prepare("
                INSERT INTO user
                (username, password, email, register_date)
                VALUES (:username, :password, :email, :register_date)
            ");
            $qRegister->bindParam(":username", $info[0]);
            $qRegister->bindParam(":password", $info[1]);
            $qRegister->bindParam(":email", $info[2]);
            $qRegister->bindParam(":register_date", date('Y-m-d H:i:s'));

            $qRegister->execute();

            return "Registration succesful.<br />";

        } catch(PDOException $e) {
            return "Registration failed.<br />";
        }
    }

    //mogelijk om te doen: SELECT :row FROM :table WHERE :row=:content? Machiel says "Nope."

    public function checkExistence($table, $row, $value) {
        try {
            $qExcistance = $this->db->prepare("
                SELECT $row
                FROM $table
                WHERE $row = :val
                    ");
            $qExcistance->bindParam(":val", $value);
            $qExcistance->execute();
            if($qExcistance->rowCount() == 0) {
                return false;
            }else {
                return true;
            }
        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }

    public function getIdByPassAndUser($username, $password) {
        try {
            $qId = $this->db->prepare("
                SELECT id
                FROM user
                WHERE username=:username AND password=:password
            ");
            $qId->bindParam(":username", $username);
            $qId->bindParam(":password", $password);
            $qId->execute();
            if($qId->rowCount() == 0) {
                return false;
            }else {
                $qId = $qId->fetch(PDO::FETCH_ASSOC);
                return $qId["id"];
            }


        } catch(PDOException $e) {
            print_r($e->getMessage());
        }
    }
}
?>
